﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using ContractManagementSystem.Entity;
using System.Globalization;

namespace ContractManagementSystem.DataAccess
{
    public class LineManagerDataAccess : LogDataAccess
    {
        private const string UserName = "Admin";

        /// <summary>
        /// Function Select All LineManager
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="activeType"></param>
        /// <returns></returns>
        public DataTable FetchAll(int activeType)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ManagerId", typeof(Guid));
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("BirthDate", typeof(DateTime));
            dataTable.Columns.Add("FullName", typeof(string));
            dataTable.Columns.Add("Gender", typeof(string));
            dataTable.Columns.Add("Email", typeof(string));
            dataTable.Columns.Add("PhoneNumber", typeof(string));
            dataTable.Columns.Add("Title", typeof(string));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            try
            {
                //log event          
                WriteEvent("Selecting...", UserName);
                int i = 0;
                var sqlCommand = new SqlCommand("sp_LineManager_SelectAll", CreateConnection())
                                     {CommandType = CommandType.StoredProcedure};
                sqlCommand.Parameters.Add(new SqlParameter("@ActiveType", activeType));
                var sqlDataReader = sqlCommand.ExecuteReader();
                if(sqlDataReader.HasRows)
                {
                    while(sqlDataReader.Read())
                    {
                        var managerId = new Guid(sqlDataReader["ManagerId"].ToString());
                        var userId = new Guid(sqlDataReader["UserId"].ToString());
                        var birthDate = DateTime.Parse(sqlDataReader["BirthDate"].ToString(), CultureInfo.CurrentCulture);
                        var fullName = sqlDataReader["FullName"].ToString();
                        var gender = FetchGender(int.Parse(sqlDataReader["Gender"].ToString(), CultureInfo.CurrentCulture));
                        var email = sqlDataReader["Email"].ToString();
                        var phoneNumber = sqlDataReader["PhoneNumber"].ToString();
                        var title = sqlDataReader["Title"].ToString();
                        var isActive = bool.Parse(sqlDataReader["IsActive"].ToString());
                        if(i==15)
                        {
                            i = 0;
                        }
                        dataTable.Rows.Add(managerId, userId, birthDate, fullName, gender, email, phoneNumber, title,isActive,i);
                        i++;
                    }
                }
                sqlDataReader.Close();
                //log event
                //WriteEvent("Select success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }
            
            return dataTable;
        }

        /// <summary>
        /// Function SearchFullName
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        
        public DataTable SearchFullName(string name)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ManagerId", typeof(Guid));
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("BirthDate", typeof(DateTime));
            dataTable.Columns.Add("FullName", typeof(string));
            dataTable.Columns.Add("Gender", typeof(string));
            dataTable.Columns.Add("Email", typeof(string));
            dataTable.Columns.Add("PhoneNumber", typeof(string));
            dataTable.Columns.Add("Title", typeof(string));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            try
            {
                //log event          
                WriteEvent("Selecting...", UserName);
                int i = 0;
                var sqlCommand = new SqlCommand("sp_LineManager_SearchFullName", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@FullName", name));
                var sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader.HasRows)
                {
                    while (sqlDataReader.Read())
                    {
                        var managerId = new Guid(sqlDataReader["ManagerId"].ToString());
                        var userId = new Guid(sqlDataReader["UserId"].ToString());
                        var birthDate = DateTime.Parse(sqlDataReader["BirthDate"].ToString(), CultureInfo.CurrentCulture);
                        var fullName = sqlDataReader["FullName"].ToString();
                        var gender = FetchGender(int.Parse(sqlDataReader["Gender"].ToString(), CultureInfo.CurrentCulture));
                        var email = sqlDataReader["Email"].ToString();
                        var phoneNumber = sqlDataReader["PhoneNumber"].ToString();
                        var title = sqlDataReader["Title"].ToString();
                        var isActive = bool.Parse(sqlDataReader["IsActive"].ToString());
                        if (i == 14)
                        {
                            i = 0;
                        }
                        dataTable.Rows.Add(managerId, userId, birthDate, fullName, gender, email, phoneNumber, title, isActive, i);
                        i++;
                    }
                }
                //log event
                //WriteEvent("Select success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }
            
            return dataTable;
        }

        /// <summary>
        /// Function FetchGender
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="gender"></param>
        /// <returns></returns>
        private static string FetchGender(int gender)
        {
            string gengertype;
            if(gender == 0)
            {
                gengertype = "Male";
            }
            else if(gender == 1)
            {
                gengertype = "Female";
            }
            else
            {
                gengertype = "Orther";
            }
            return gengertype;
        }

        /// <summary>
        /// Function Select By Id LineManager
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="lineManagerEntity"></param>
        /// <returns></returns>
        public DataTable FetchById(LineManagerEntity lineManagerEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);
                var sqlCommand = new SqlCommand("sp_LineManager_GetByID", CreateConnection());
                sqlCommand.Parameters.Add(new SqlParameter("@ManagerId", lineManagerEntity.LineManagerId));
                sqlCommand.CommandType = CommandType.StoredProcedure;
                var dataAdapter = new SqlDataAdapter(sqlCommand);
                dataAdapter.Fill(dataTable);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }
            //log event
            //WriteEvent("Select success!", UserName);
            return dataTable;
        }

        /// <summary>
        /// Function Insert LineManager
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="lineManagerEntity"></param>
        /// <returns></returns>
        public bool Insert(LineManagerEntity lineManagerEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Inserting...", UserName);
                var sqlCommand = new SqlCommand("sp_LineManager_Insert", CreateConnection())
                                     {CommandType = CommandType.StoredProcedure};
                sqlCommand.Parameters.Add(new SqlParameter("@UserId", lineManagerEntity.UserId));
                sqlCommand.Parameters.Add(new SqlParameter("@BirthDate", lineManagerEntity.Birthday));
                sqlCommand.Parameters.Add(new SqlParameter("@FullName", lineManagerEntity.FullName));
                sqlCommand.Parameters.Add(new SqlParameter("@Gender", lineManagerEntity.Gender));
                sqlCommand.Parameters.Add(new SqlParameter("@Email", lineManagerEntity.Email));
                sqlCommand.Parameters.Add(new SqlParameter("@PhoneNumber", lineManagerEntity.PhoneNumber));
                sqlCommand.Parameters.Add(new SqlParameter("@Title", lineManagerEntity.Title));
                sqlCommand.ExecuteNonQuery();
                //log event
                WriteEvent("Insert success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            finally
            {
                CloseConnection();
            }
            
            return true;
        }

        /// <summary>
        /// Function Update LineManager
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="lineManagerEntity"></param>
        /// <returns></returns>
        public bool Update(LineManagerEntity lineManagerEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Updating...", UserName);
                var sqlCommand = new SqlCommand("sp_LineManager_Update", CreateConnection())
                                     {CommandType = CommandType.StoredProcedure};
                sqlCommand.Parameters.Add(new SqlParameter("@ManagerId", lineManagerEntity.LineManagerId));
                sqlCommand.Parameters.Add(new SqlParameter("@BirthDate", lineManagerEntity.Birthday));
                sqlCommand.Parameters.Add(new SqlParameter("@FullName", lineManagerEntity.FullName));
                sqlCommand.Parameters.Add(new SqlParameter("@Gender", lineManagerEntity.Gender));
                sqlCommand.Parameters.Add(new SqlParameter("@Email", lineManagerEntity.Email));
                sqlCommand.Parameters.Add(new SqlParameter("@PhoneNumber", lineManagerEntity.PhoneNumber));
                sqlCommand.Parameters.Add(new SqlParameter("@Title", lineManagerEntity.Title));
                sqlCommand.Parameters.Add(new SqlParameter("@IsActive", lineManagerEntity.IsActive));
                sqlCommand.ExecuteNonQuery();
                //log event
                WriteEvent("Update success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            finally
            {
                CloseConnection();
            }
            
            return true;
        }

        /// <summary>
        /// Function Delete LineManager
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="lineManagerEntity"></param>
        /// <returns></returns>
        public bool Delete(LineManagerEntity lineManagerEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Deleting...", UserName);
                var sqlCommand = new SqlCommand("sp_LineManager_Delete", CreateConnection())
                                     {CommandType = CommandType.StoredProcedure};
                sqlCommand.Parameters.Add(new SqlParameter("@ManagerId", lineManagerEntity.LineManagerId));
                sqlCommand.ExecuteNonQuery();
                //log event
                WriteEvent("Delete success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Delete fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return true;
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            finally
            {
                CloseConnection();
            }
            
            return true;
        }
    }
}
